﻿using Common;
using DBUtility;
using Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace BaseDal
{
    /// <summary>
    /// PurchaseOrderDal 类实现了付款申请的数据访问层。
    /// 该类采用单例模式，确保只有一个实例存在。
    /// </summary>
    public class PurchaseOrderDal
    {
        // 使用 Lazy<T> 实现单例模式，确保实例的惰性初始化和线程安全。
        private static readonly Lazy<PurchaseOrderDal> _instance = new Lazy<PurchaseOrderDal>(() => new PurchaseOrderDal());

        /// <summary>
        /// 获取类的单例实例。
        /// </summary>
        public static PurchaseOrderDal Instance { get; } = _instance.Value;

        // 私有构造函数，防止外部实例化该类。
        private PurchaseOrderDal()
        { }

        private const string tableH = "PurchaseOrderHead";

        private const string tableB = "PurchaseOrderBody";

        /// <summary>
        /// 查询数据是否存在
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public bool IsExist(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from " + tableH);
            strSql.Append(" where IsDelete=0 " + strWhere);
            return SqlHelper.Exists(strSql.ToString());
        }

        /// <summary>
        /// 查询SQL语句
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public StringBuilder GetSelectSql(string strWhere = "")
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT ID,PO_OrderNo,PO_OrderDate,PO_Buyer,PO_CompanyId,PO_CompanyName,PO_DeptId,PO_DeptName,");
            sb.Append("PO_DeliveryDate,PO_SupplierId,PO_SupplierName,PO_ReceivingInfo,PO_Remarks,PO_AuditStatus,PO_TotalAmount ");
            sb.Append("FROM " + tableH);
            sb.Append(" Where IsDelete=0 ");

            if (!string.IsNullOrEmpty(strWhere))
            {
                sb.Append(strWhere);
            }

            return sb;
        }

        /// <summary>
        /// 查询分页数据
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public DataTable GetPageList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder sb = GetSelectSql(strWhere);
            string countSql = PageHelper.CreateCountingSql(sb.ToString());
            recordCount = Convert.ToInt32(SqlHelper.GetSingle(countSql));
            string pageSql = PageHelper.CreatePageSql(recordCount, pageSize, pageIndex, sb.ToString(), filedOrder);
            DataSet ds = SqlHelper.Query(pageSql);
            return ds.Tables[0];
        }

        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public List<PurchaseOrderHead> GetList(string strWhere)
        {
            StringBuilder sb = GetSelectSql(strWhere);
            DataSet ds = SqlHelper.Query(sb.ToString());
            return CommonOperation.ConvertDataTableToModelList<PurchaseOrderHead>(ds.Tables[0]);
        }

        /// <summary>
        /// 根据id查询实体类
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public PurchaseOrderHead GetModelById(decimal id)
        {
            // 查询主表
            PurchaseOrderHead model;
            StringBuilder sb = GetSelectSql(" and ID=@id");

            SqlParameter[] parameters =
            {
                new SqlParameter("@id",id)
            };
            DataSet ds = SqlHelper.Query(sb.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                model = CommonOperation.DataTableToModel<PurchaseOrderHead>(ds.Tables[0]);
                // 查询子表
                model.ItemList = GetItemList((int)model.ID);

                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 根据主表ID查询所有物品
        /// </summary>
        /// <param name="headID"></param>
        /// <returns></returns>
        public List<PurchaseOrderBody> GetItemList(int headID)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT ID,HeadID,PO_ItemID,PO_ItemName,PO_Specs,PO_Unit,PO_Price,PO_ApplyQty,");
            sb.Append("PO_ReadyQty,PO_BuyQty,PO_Texrate,PO_TotalCost,PO_ApplyNo ");
            sb.Append("FROM " + tableB);
            sb.Append(" Where IsDelete=0 and HeadID=@headID ");
            SqlParameter[] param1 = { new SqlParameter("@headID", headID) };
            DataSet ds = SqlHelper.Query(sb.ToString(), param1);
            return CommonOperation.ConvertDataTableToModelList<PurchaseOrderBody>(ds.Tables[0]);
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool AddPurchaseOrder(PurchaseOrderHead model)
        {
            // 定义主表SQL语句和参数
            StringBuilder hSql = new StringBuilder();
            hSql.Append("insert into " + tableH + " (");
            hSql.Append("PO_OrderNo,PO_OrderDate,PO_Buyer,PO_CompanyId,PO_CompanyName,PO_DeptId,PO_DeptName,");
            hSql.Append("PO_DeliveryDate,PO_SupplierId,PO_SupplierName,PO_ReceivingInfo,PO_Remarks,PO_AuditStatus,PO_TotalAmount)");
            hSql.Append(" OUTPUT inserted.ID values (");
            hSql.Append("@orderNo,@orderDate,@buyer,@companyId,@companyName,@deptId,@deptName,");
            hSql.Append("@deliveryDate,@supplierId,@supplierName,@receivingInfo,@remarks,@auditStatus,@totalAmount)");

            string connectionString = SqlHelper.connectionString;
            Action<SqlConnection, SqlTransaction> sqlAction = (connection, transaction) =>
            {
                using (SqlCommand command1 = new SqlCommand(hSql.ToString(), connection, transaction))
                {
                    command1.CommandType = CommandType.Text;
                    command1.Parameters.AddWithValue("@orderNo", model.PO_OrderNo);
                    command1.Parameters.AddWithValue("@orderDate", model.PO_OrderDate);
                    command1.Parameters.AddWithValue("@buyer", model.PO_Buyer);
                    command1.Parameters.AddWithValue("@companyId", model.PO_CompanyId);
                    command1.Parameters.AddWithValue("@companyName", model.PO_CompanyName);
                    command1.Parameters.AddWithValue("@deptId", model.PO_DeptId);
                    command1.Parameters.AddWithValue("@deptName", model.PO_DeptName);
                    command1.Parameters.AddWithValue("@deliveryDate", model.PO_DeliveryDate);
                    command1.Parameters.AddWithValue("@supplierId", model.PO_SupplierId);
                    command1.Parameters.AddWithValue("@supplierName", model.PO_SupplierName);
                    command1.Parameters.AddWithValue("@receivingInfo", model.PO_ReceivingInfo);
                    command1.Parameters.AddWithValue("@remarks", model.PO_Remarks);
                    command1.Parameters.AddWithValue("@auditStatus", model.PO_AuditStatus);
                    command1.Parameters.AddWithValue("@totalAmount", model.PO_TotalAmount);
                    command1.Connection = connection; // 确保连接已设置

                    // 执行命令并获取自增长ID
                    object result = command1.ExecuteScalar();
                    long newID = 0;
                    if (result != null && result is decimal) // 检查是否为null并且能否转换为decimal
                    {
                        newID = Convert.ToInt64(result);
                        model.ID = newID;
                    }

                    // 子表数据
                    List<PurchaseOrderBody> itemList = model.ItemList;
                    if (itemList != null && itemList.Count > 0)
                    {
                        SqlCommand command2;

                        StringBuilder itemSql;
                        foreach (PurchaseOrderBody itemModel in itemList)
                        {
                            itemSql = new StringBuilder();
                            itemSql.Append("insert into " + tableB + "(");
                            itemSql.Append("HeadID,PO_ItemID,PO_ItemName,PO_Specs,PO_Unit,PO_Price,PO_ApplyQty,PO_ReadyQty,PO_BuyQty,PO_Texrate,");
                            itemSql.Append("PO_TotalCost,PO_ApplyNo)");
                            itemSql.Append(" values (");
                            itemSql.Append("@headID,@itemID,@itemName,@specs,@unit,@price,@applyQty,@readyQty,@buyQty,@texrate,@totalCost,@applyno)");

                            using (command2 = new SqlCommand(itemSql.ToString(), connection, transaction))
                            {
                                command2.CommandType = CommandType.Text;
                                command2.Parameters.AddWithValue("@headID", newID);
                                command2.Parameters.AddWithValue("@itemID", itemModel.PO_ItemID);
                                command2.Parameters.AddWithValue("@itemName", itemModel.PO_ItemName);
                                command2.Parameters.AddWithValue("@specs", itemModel.PO_Specs);
                                command2.Parameters.AddWithValue("@unit", itemModel.PO_Unit);
                                command2.Parameters.AddWithValue("@price", itemModel.PO_Price);
                                command2.Parameters.AddWithValue("@applyQty", itemModel.PO_ApplyQty);
                                command2.Parameters.AddWithValue("@readyQty", itemModel.PO_ReadyQty);
                                command2.Parameters.AddWithValue("@buyQty", itemModel.PO_BuyQty);
                                command2.Parameters.AddWithValue("@texrate", itemModel.PO_Texrate);
                                command2.Parameters.AddWithValue("@totalCost", itemModel.PO_TotalCost);
                                command2.Parameters.AddWithValue("@applyno", itemModel.PO_ApplyNo);

                                command2.ExecuteNonQuery();
                            }
                        }
                    }
                }
            };

            // 调用DatabaseHelper中的静态方法ExecuteTransaction
            SqlHelper.ExecuteTransaction(sqlAction, connectionString);

            return true;
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool UpdatePurchaseOrder(PurchaseOrderHead model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + tableH + " set ");
            strSql.Append("PO_OrderDate=@orderDate,");
            strSql.Append("PO_Buyer=@buyer,");
            strSql.Append("PO_CompanyId=@companyId,");
            strSql.Append("PO_CompanyName=@companyName,");
            strSql.Append("PO_DeptId=@deptId,");
            strSql.Append("PO_DeptName=@deptName,");
            strSql.Append("PO_DeliveryDate=@deliveryDate,");
            strSql.Append("PO_SupplierId=@supplierId,");
            strSql.Append("PO_SupplierName=@supplierName,");
            strSql.Append("PO_ReceivingInfo=@receivingInfo,");
            strSql.Append("PO_Remarks=@remarks,");
            strSql.Append("PO_AuditStatus=@auditStatus,");
            strSql.Append("PO_TotalAmount=@totalAmount ");
            strSql.Append(" where ID=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@orderDate", model.PO_OrderDate),
                    new SqlParameter("@buyer", model.PO_Buyer),
                    new SqlParameter("@companyId", model.PO_CompanyId),
                    new SqlParameter("@companyName", model.PO_CompanyName),
                    new SqlParameter("@deptId", model.PO_DeptId),
                    new SqlParameter("@deptName", model.PO_DeptName),
                    new SqlParameter("@deliveryDate", model.PO_DeliveryDate),
                    new SqlParameter("@supplierId", model.PO_SupplierId),
                    new SqlParameter("@supplierName", model.PO_SupplierName),
                    new SqlParameter("@receivingInfo", model.PO_ReceivingInfo),
                    new SqlParameter("@remarks", model.PO_Remarks),
                    new SqlParameter("@auditStatus", model.PO_AuditStatus),
                    new SqlParameter("@totalAmount", model.PO_TotalAmount),
                    new SqlParameter("@id", model.ID)
            };
            List<CommandInfo> sqllist = new List<CommandInfo>();
            CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters);
            sqllist.Add(cmd);

            //先删除子表数据
            StringBuilder strSql2 = new StringBuilder();
            strSql2.Append("delete from " + tableB + " where HeadID=@headID ");
            SqlParameter[] parameters2 = { new SqlParameter("@headID", model.ID) };
            cmd = new CommandInfo(strSql2.ToString(), parameters2);
            sqllist.Add(cmd);

            //添加子表数据
            if (model.ItemList != null)
            {
                StringBuilder itemSql;
                foreach (PurchaseOrderBody modelt in model.ItemList)
                {
                    itemSql = new StringBuilder();
                    itemSql.Append("insert into " + tableB + "(");
                    itemSql.Append("HeadID,PO_ItemID,PO_ItemName,PO_Specs,PO_Unit,PO_Price,PO_ApplyQty,PO_ReadyQty,PO_BuyQty,PO_Texrate,PO_TotalCost,PO_ApplyNo)");
                    itemSql.Append(" values (");
                    itemSql.Append("@headID,@itemID,@itemName,@specs,@unit,@price,@applyQty,@readyQty,@buyQty,@texrate,@totalCost,@applyNo)");
                    SqlParameter[] parameters3 = {
                            new SqlParameter("@headID", model.ID),
                            new SqlParameter("@itemID", modelt.PO_ItemID),
                            new SqlParameter("@itemName", modelt.PO_ItemName),
                            new SqlParameter("@specs", modelt.PO_Specs),
                            new SqlParameter("@unit", modelt.PO_Unit),
                            new SqlParameter("@price", modelt.PO_Price),
                            new SqlParameter("@applyQty", modelt.PO_ApplyQty),
                            new SqlParameter("@readyQty", modelt.PO_ReadyQty),
                            new SqlParameter("@buyQty", modelt.PO_BuyQty),
                            new SqlParameter("@texrate", modelt.PO_Texrate),
                            new SqlParameter("@totalCost", modelt.PO_TotalCost),
                            new SqlParameter("@applyNo", modelt.PO_ApplyNo)
                    };
                    cmd = new CommandInfo(itemSql.ToString(), parameters3);
                    sqllist.Add(cmd);
                }
            }
            int rowsAffected = SqlHelper.ExecuteSqlTran(sqllist);
            return rowsAffected > 0;
        }

        /// <summary>
        /// 删除一条数据，及子表所有相关数据
        /// </summary>
        public bool DeletePurchaseOrder(int ID)
        {
            List<CommandInfo> sqllist = new List<CommandInfo>();
            //删除子表
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + tableB + " set IsDelete=1");
            strSql.Append(" where HeadID=@headID");
            SqlParameter[] parameters = { new SqlParameter("@headID", ID) };
            CommandInfo cmd = new CommandInfo(strSql.ToString(), parameters);
            sqllist.Add(cmd);
            //删除主表
            StringBuilder strSql2 = new StringBuilder();
            strSql2.Append("update " + tableH + " set IsDelete=1");
            strSql2.Append(" where ID=@id");
            SqlParameter[] parameters2 = { new SqlParameter("@id", ID) };
            cmd = new CommandInfo(strSql2.ToString(), parameters2);
            sqllist.Add(cmd);

            int rowsAffected = SqlHelper.ExecuteSqlTran(sqllist);
            return rowsAffected > 0;
        }

        /// <summary>
        /// 修改审核状态（0：审批中；1：已审批；2：暂存）
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int UpdateStatus(int id, int status)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("Update " + tableH + " SET ");
            sb.Append("PO_AuditStatus=@status ");
            sb.Append("WHERE ID=@id");
            SqlParameter[] parameters = {
                new SqlParameter("@status",status),
                new SqlParameter("@id",id),
            };
            int result = SqlHelper.ExecuteSql(sb.ToString(), parameters);
            return result;
        }

        /// <summary>
        /// 通过申请单号与 物品编号获取物品
        /// </summary>
        /// <param name="headID"></param>
        /// <param name="itemID"></param>
        /// <returns></returns>
        public PurchaseOrderBody GetItemByHeadIDAndItemID(decimal headID, string itemID)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT ID,HeadID,PO_ItemID,PO_ItemName,PO_Specs,PO_Unit,PO_Price,PO_ApplyQty,");
            sb.Append("PO_ReadyQty,PO_BuyQty,PO_Texrate,PO_TotalCost,PO_ApplyNo ");
            sb.Append("FROM " + tableB);
            sb.Append(" Where IsDelete=0 and HeadID=@headID and PO_ItemID=@itemID ");
            SqlParameter[] param1 = {
                new SqlParameter("@headID", headID),
                new SqlParameter("@itemID", itemID)
            };
            DataSet ds = SqlHelper.Query(sb.ToString(), param1);
            return CommonOperation.DataTableToModel<PurchaseOrderBody>(ds.Tables[0]);
        }

        /// <summary>
        /// 采购变更审核通过时，修改采购单子表购买数量
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public bool UpdOrderQtytWithAudit(List<PurchaseOrderBody> list)
        {
            List<CommandInfo> sqllist = new List<CommandInfo>();
            CommandInfo cmd;
            StringBuilder strSql;
            foreach (PurchaseOrderBody item in list)
            {
                strSql = new StringBuilder();
                strSql.Append("update " + tableB + " set ");
                strSql.Append("PO_BuyQty=@buyQty,");
                strSql.Append("PO_Texrate=@texrate,");
                strSql.Append("PO_TotalCost=@totalCost ");
                strSql.Append(" where ID=@id");
                SqlParameter[] parameters = {
                    new SqlParameter("@buyQty", item.PO_BuyQty),
                    new SqlParameter("@texrate", item.PO_Texrate),
                    new SqlParameter("@totalCost", item.PO_TotalCost),
                    new SqlParameter("@id", item.ID)
            };
                cmd = new CommandInfo(strSql.ToString(), parameters);
                sqllist.Add(cmd);
            }

            int rowsAffected = SqlHelper.ExecuteSqlTran(sqllist);
            return rowsAffected > 0;
        }
    }
}